package com.xmy.study.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.study.entity.ChaPlan;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

@Mapper
public interface ChaPlanMapper02 extends BaseMapper<ChaPlan> {

    @Select("SELECT c.* " +
            "FROM cha_plan c " +
            "INNER JOIN ( " +
            "    SELECT student_id, MAX(school_date) as max_school_date " +
            "    FROM cha_plan " +
            "    GROUP BY student_id " +
            ") as latest_cha_plan ON c.student_id = latest_cha_plan.student_id AND c.school_date = latest_cha_plan.max_school_date " +
            "${ew.customSqlSegment}")
    List<ChaPlan> getClearanceRateChaPlan(@Param(Constants.WRAPPER) Wrapper wrapper);


    @Select({"<script>",
            "select * from (",
            "SELECT",
                    "t2.school_id,",
                    "t2.year_part,",
                    "t1.quarter_num,",
                    "t1.subjects_id,",
                    "t1.course_version_id,",
                    "t2.division_grade_id AS grade_id,",
                    "t2.division_teacher_id AS teacher_id,",
                    "t2.class_type_id,",
                    "t2.year_class_id,",
                    "t2.student_id,",
                    "IFNULL(is_finish,0) as is_finish,",
                    "t1.task_course_num as lesson_num,",
                    "IFNULL(t1.lowest_shutnum,0) as lowest_shutnum,",
                    "IFNULL(t3.right_num,0) as right_num,",
                    "IFNULL(t3.amend_num,0) as amend_num,",
                    "student_name",
                    "FROM",
                    "( SELECT * FROM edu_task WHERE deleted=0 ",
                        "<when test='isHistory==0'>",
                            "and task_course_num = ${lessonNum}",
                        "</when>",
                        "<when test='isHistory==1'>",
                            "and task_course_num &lt;= ${lessonNum}",
                        "</when>",
                    " ) AS t1",
                    "INNER JOIN (",
                    "SELECT",
                      "t01.*,",
                      "t03.year_class_id,",
                      "t03.grade_id AS division_grade_id,",
                      "t04.teacher_id AS division_teacher_id,",
                      //"t04.course_type_id as class_type_id,",
                      "t05.class_type_id as class_type_id,",
                      "t03.student_id,",
                        "t03.student_name ",
                    "FROM",
                      "(",
                      "SELECT",
                        "* ",
                      "FROM",
                        "xmy_erp_db.course_scheduling_detail ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND QUARTER = ${quarterNum} ",
                        "AND lesson_type = 3 ",
                        "AND subjects_id = ${subjectsId} ",
                        "AND deleted = 0 ",
                      ") AS t01",
                      "LEFT JOIN (",
                      "SELECT",
                        "* ",
                      "FROM",
                        "xmy_erp_db.student_grade ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                        "AND lesson_type = 3 ",
                        "AND deleted = 0 ",
                        "AND subjects_id = ${subjectsId} ",
                        "AND reading_status in(1,5) ",
                        "AND STATUS &lt;&gt; 3 ",
                        "AND deleted = 0 ",
                      "GROUP BY",
                        "student_id ",
                      ") AS t02 ON t01.grade_id = t02.grade_id",
                      "INNER JOIN (",
                      "SELECT",
                        "gt.*,",
                        "st.name as student_name",
                      "FROM",
                        "xmy_erp_db.student_grade as gt",
                        "LEFT JOIN xmy_erp_db.student as st on gt.student_id = st.id",
                      "WHERE",
                        "gt.year_part = ${yearPart} ",
                        "AND gt.quarter_num = ${quarterNum} ",
                        "AND gt.lesson_type = 1 ",
                        "AND gt.subjects_id = ${subjectsId} ",
                        "AND gt.STATUS &lt;&gt; 3 ",
                        "AND gt.reading_status = 1 ",
                        "AND gt.is_last = 1 ",
                        "AND gt.deleted = 0 ",
                        "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                        "and st.name like '%${studentName}%'",
                        "</when>",
                      "GROUP BY",
                        "student_id ",
                      ") AS t03 ON t02.student_id = t03.student_id ",
                      "LEFT JOIN xmy_erp_db.grade as t04 on t03.grade_id = t04.id",
                      "LEFT JOIN per_student_account as t05 on t05.student_id = t02.student_id",
                    ") AS t2 ON t1.year_part = t2.year_part ",
                    "AND t1.quarter_num = t2.QUARTER ",
                    "AND t1.year_class_id = t2.year_class_id ",
                    "and t1.class_type_id = t2.class_type_id",
                    "and t1.subjects_id = t2.subjects_id",
                    "AND t2.school_date BETWEEN t1.start_task_date ",
                    "AND t1.task_date",
                    "LEFT JOIN (",
                    "SELECT",
                      "student_id,",
                        "class_type_id,",
                        "is_finish,",
                      "year_part,",
                      "quarter_num,",
                      "IFNULL( lesson_num, 0 ) AS lesson_num,",
                      "right_num,",
                      "amend_num ",
                    "FROM",
                      "cha_plan ",
                    "WHERE",
                      "year_part = ${yearPart} ",
                      "AND quarter_num = ${quarterNum} ",
                      "and is_last = 1",
                    ") AS t3 ON t1.year_part = t3.year_part ",
                    "AND t1.quarter_num = t3.quarter_num ",
                    "and t2.student_id = t3.student_id",
                    "AND t2.class_type_id = t3.class_type_id",
                    "and t3.lesson_num = t1.task_course_num",
            ") as t",
            "where 1=1",
            "and lesson_num &lt;= ${lessonNum} and (right_num+amend_num) &lt; lowest_shutnum",
            //"<when test='isHistory==0'>",
            //    "AND ( lesson_num = ${lessonNum} OR(lesson_num &lt; ${lessonNum} AND is_finish = 0 ))",
            //"</when>",
            //"<when test='isHistory==1'>",
            //    "and lesson_num = ${lessonNum} AND is_finish = 0",
            //"</when>",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; '>",
            "and school_id in(${schoolIdStr})",
            "</when>",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and year_class_id in(${yearClassIdStr})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "and teacher_id = ${teacherId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "and grade_id = ${gradeId}",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and class_type_id = ${classTypeId}",
            "</when>",
            "GROUP BY year_part,quarter_num,student_id,lesson_num",
    "</script>"})
    public IPage<ChaPlan> getChaPanAddList(IPage<?> page,
                                           @Param("yearPart") String yearPart,
                                           @Param("quarterNum") String quarterNum,
                                           @Param("schoolIdStr") String schoolIdStr,
                                           @Param("teacherId") String teacherId,
                                           @Param("gradeId") String gradeId,
                                           @Param("yearClassIdStr") String yearClassIdStr,
                                           @Param("classTypeId") String classTypeId,
                                           @Param("lessonNum") String lessonNum,
                                           @Param("isHistory") Integer isHistory,
                                           @Param("status") String status,
                                           @Param("studentName") String studentName,
                                           @Param("subjectsId") Long subjectsId);

    @Select({"<script>",
            "SELECT",
                    "*,",
                    "SUM( lowest_shutnum ) AS lowest_shutnum_all,",
                    "SUM( right_num ) AS right_num_all,",
            "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
                "SUM( right_num_date_no_sum ) AS right_num_date,",
            "</when>",
            "<when test='rightNumStartDate == &apos;&apos; and rightNumEndDate == &apos;&apos;'>",
                "0 AS right_num_date,",
            "</when>",
                    "SUM( amend_num ) AS amend_num_all,",
                    "SUM( lowest_shutnum - right_num ) AS error_num_all,",
                    "count(*) AS show_pass_num,",
                    "SUM( CASE WHEN lowest_shutnum &lt;= ( right_num + amend_num ) and lowest_shutnum  &lt;&gt; 0  THEN 1 ELSE 0 END ) AS yet_pass_num,",
                    "SUM( CASE WHEN (lowest_shutnum &gt; ( right_num + amend_num )) or (lowest_shutnum  = 0) THEN 1 ELSE 0 END ) AS not_pass_num,",
                    "SUM( CASE WHEN date_pass_num_in &gt; 0 THEN 1 ELSE 0 END ) AS date_pass_num ",
                    "FROM",
                    "(",
                    "SELECT",
                      "school_id,",
                      "year_part,",
                      "quarter_num,",
                      "grade_id,",
                      "teacher_id,",
                      "class_type_id,",
                      "year_class_id,",
                      "student_id,",
                      "lesson_num,",
                      "add_date,",
                      "SUM( lowest_shutnum ) AS lowest_shutnum,",
            "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
            "SUM( CASE WHEN add_date BETWEEN '${rightNumStartDate}' AND '${rightNumEndDate}' THEN right_num ELSE 0 END ) AS right_num_date_no_sum,",
            "</when>",
            "<when test='rightNumStartDate == &apos;&apos; and rightNumEndDate == &apos;&apos;'>",
            "0 AS right_num_date_no_sum,",
            "</when>",
                      "SUM( right_num ) AS right_num,",
                      "SUM( amend_num ) AS amend_num,",
                      "SUM( CASE WHEN lowest_shutnum &lt;= ( right_num + amend_num ) AND add_date BETWEEN '${passDateStart}' AND '${passDateEnd}' THEN 1 ELSE 0 END ) AS date_pass_num_in ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t01.school_id,",
                        "t01.year_part,",
                        "t01.quarter_num,",
                        "t02.course_version_id,",
                        "t01.grade_id,",
                        "t01.teacher_id,",
                        "t01.class_type_id,",
                        "t01.year_class_id,",
                        "t01.student_id,",
                        "t02.task_course_num AS lesson_num,",
                        "IFNULL( t02.lowest_shutnum, 0 ) AS lowest_shutnum,",
                        "IFNULL( t03.right_num, 0 ) AS right_num,",
                        "IFNULL( t03.amend_num, 0 ) AS amend_num,",
                        "add_date ",
                      "FROM",
                        "(",
                        "SELECT",
                          "t1.*,",
                          //"t2.course_type_id AS class_type_id,",
                          "t3.class_type_id,",
                          "t2.teacher_id ",
                        "FROM",
                          "xmy_erp_db.student_grade AS t1",
                          "LEFT JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id ",
                          "LEFT JOIN per_student_account AS t3 ON t1.student_id = t3.student_id",
                        "WHERE",
                          "t1.year_part = ${yearPart} ",
                          "AND t1.quarter_num = ${quarterNum} ",
                          "AND t1.lesson_type = 1 ",
                          "AND t1.deleted = 0 ",
                          "AND t1.subjects_id = ${subjectsId} ",
                          "AND t1.is_last = 1 ",
                        "<when test='isBeforQuarter == 0 and readingType == 1'>",
                            "AND t1.reading_status IN (1) ",
                        "</when>",
                        "<when test='isBeforQuarter == 1 and readingType == 1'>",
                            "AND t1.reading_status IN (1,5)",
                        "</when>",
                        "<when test='readingType == 2'>",
                            "AND ((t1.reading_status IN (1,5)) or (t1.reading_status in(3,4) and use_course_count > 0)) ",
                        "</when>",
                          "AND t1.STATUS &lt;&gt; 3 ",
                          "AND t1.deleted = 0 ",
                        "GROUP BY",
                          "student_id ",
                        ") AS t01",
                        "LEFT JOIN ( SELECT * FROM edu_task WHERE deleted = 0 and task_course_num BETWEEN ${lessonNumStart} and ${lessonNum} ) AS t02 ON t01.year_part = t02.year_part ",
                        "AND t01.quarter_num = t02.quarter_num ",
                        "AND t01.subjects_id = t02.subjects_id ",
                        "AND t01.class_type_id = t02.class_type_id",
                        "AND t01.year_class_id = t02.year_class_id",
                        "LEFT JOIN (",
                        "SELECT",
                          "student_id,",
                          "add_date,",
                          "lesson_num_finish,",
                          "year_class_id,",
                          "course_version_id,",
                            "class_type_id,",
                          "year_part,",
                          "quarter_num,",
                          "IFNULL( lesson_num, 0 ) AS lesson_num,",
                          "right_num,",
                          "is_last,",
                          "amend_num ",
                        "FROM",
                          "cha_plan ",
                        "WHERE",
                          "year_part = ${yearPart} ",
                          "AND quarter_num = ${quarterNum} ",
                        ") AS t03 ON t01.year_part = t03.year_part ",
                        "AND t01.quarter_num = t03.quarter_num ",
                        "AND t01.student_id = t03.student_id ",
                        "AND t02.year_class_id = t03.year_class_id",
                        "AND t02.course_version_id = t03.course_version_id",
                        "AND t02.class_type_id = t03.class_type_id ",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                            "and t01.subjects_id = ${subjectsId}",
                        "</when>",
                        //"<when test='isAll == 0'>",
                        //    "AND t02.task_course_num = t03.lesson_num",
                        //    "AND t02.task_course_num = t03.lesson_num_finish",
                        //"</when>",
                        //"<when test='isAll == 1'>",
                        //    "AND t02.task_course_num = t03.lesson_num",
                        //    "and t03.is_last = 1",
                        //"</when>",
                        "AND t02.task_course_num = t03.lesson_num",
                        "and t03.is_last = 1",
                      ") AS t ",
                    "GROUP BY",
                      "student_id ",
                    ") AS tt",
                    "where 1=1",
                    "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; and (type == 2)'>",
                    "and school_id in(${schoolIdStr})",
                    "</when>",
                    "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
                    "and year_class_id in(${yearClassIdStr})",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                    "and class_type_id = ${classTypeId}",
                    "</when>",
                    "<when test='type ==3'>",
                    "  and teacher_id in(select admin_id from xmy_erp_db.role_admin where role_id = 1568160887369994242)",
                    "</when>",
                    "<when test='type == 1 or type == 3'>",
                    "GROUP BY teacher_id",
                    "</when>",
                    "<when test='type == 2'>",
                    "GROUP BY school_id,teacher_id",
                    "</when>",
                    "<when test='type == 4'>",
                    "GROUP BY school_id",
                    "</when>",
            "</script>"})
    public List<ChaPlan> getPassRadeList(@Param("yearPart") String yearPart,
                                         @Param("quarterNum") String quarterNum,
                                         @Param("schoolIdStr") String schoolIdStr,
                                         @Param("yearClassIdStr") String yearClassIdStr,
                                         @Param("type") String type,
                                         @Param("lessonNumStart") String lessonNumStart,
                                         @Param("lessonNum") String lessonNum,
                                         @Param("classTypeId") String classTypeId,
                                         @Param("readingType") String readingType,
                                         @Param("lessonNow") String lessonNow,
                                         @Param("isAll") String isAll,
                                         @Param("passDate") String passDate,
                                         @Param("passDateStart") String passDateStart,
                                         @Param("passDateEnd") String passDateEnd,
                                         @Param("subjectsId") Long subjectsId,
                                         @Param("isBeforQuarter") String isBeforQuarter,
                                         @Param("readingStatusStr") String readingStatusStr,
                                         @Param("rightNumStartDate") String rightNumStartDate,
                                         @Param("rightNumEndDate") String rightNumEndDate);


    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "(",
                    "SELECT",
                      "t01.id,",
                      "t01.school_id,",
                      "t01.year_part,",
                      "t01.quarter_num,",
                      "t02.course_version_id,",
                      "t01.grade_id,",
                      "t01.teacher_id,",
                      "t01.class_type_id,",
                      "t01.year_class_id,",
                      "t01.student_id,",
                      "t02.task_course_num AS lesson_num,",
                      "IFNULL( t02.lowest_shutnum, 0 ) AS lowest_shutnum,",
                      "IFNULL( t03.right_num, 0 ) AS right_num,",
                      "IFNULL( t03.amend_num, 0 ) AS amend_num,",
                      "add_date ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.*,",
                        //"t2.course_type_id AS class_type_id,",
                        "t3.class_type_id AS class_type_id,",
                        "t2.teacher_id ",
                      "FROM",
                        "xmy_erp_db.student_grade AS t1",
                        "LEFT JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id ",
                        "LEFT JOIN per_student_account AS t3 ON t1.student_id = t3.student_id",
                      "WHERE",
                        "t1.year_part = ${yearPart} ",
                        "AND t1.quarter_num = ${quarterNum}",
                        "AND t1.lesson_type = 1",
                        "AND t1.deleted = 0 ",
                        "AND t1.subjects_id = ${subjectsId} ",
                        "AND t1.is_last = 1 ",
                        "<when test='isBeforQuarter == 0 and readingType == 1'>",
                            "AND t1.reading_status IN (1) ",
                        "</when>",
                        "<when test='isBeforQuarter == 1 and readingType == 1'>",
                            "AND t1.reading_status IN (1,5)",
                        "</when>",
                        "<when test='readingType == 2'>",
                            "AND ((t1.reading_status IN (1,5)) or (t1.reading_status in(3,4) and use_course_count > 0)) ",
                        "</when>",
                        "AND t1.STATUS &lt;&gt; 3 ",
                        "AND t1.deleted = 0 ",
                        "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                            "and t1.grade_id = ${gradeId}",
                        "</when>",
                      "GROUP BY",
                        "student_id ",
                      ") AS t01",
                      "LEFT JOIN ( SELECT * FROM edu_task WHERE deleted = 0 and task_course_num BETWEEN ${lessonNumStart} and ${lessonNum}) AS t02 ON t01.year_part = t02.year_part ",
                      "AND t01.quarter_num = t02.quarter_num ",
                      "AND t01.subjects_id = t02.subjects_id ",
                      "AND t01.class_type_id = t02.class_type_id",
                      "AND t01.year_class_id = t02.year_class_id",
            "<when test='rightNumStartDate == &apos;&apos; and rightNumEndDate == &apos;&apos;'>",
                      "LEFT JOIN (",
            "</when>",
            "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
                "INNER JOIN (",
            "</when>",
                      "SELECT",
                        "student_id,",
                        "add_date,",
                        "lesson_num_finish,",
                        "year_class_id,",
                        "course_version_id,",
                        "class_type_id,",
                        "year_part,",
                        "quarter_num,",
                        "IFNULL( lesson_num, 0 ) AS lesson_num,",
                        "right_num,",
                        "is_last,",
                        "amend_num ",
                      "FROM",
                        "cha_plan ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum}",
                        "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
                            "AND add_date BETWEEN '${rightNumStartDate}' AND '${rightNumEndDate}' ",
                            "AND right_num &gt; 0 ",
                        "</when>",
                      ") AS t03 ON t01.year_part = t03.year_part ",
                      "AND t01.quarter_num = t03.quarter_num ",
                      "AND t01.student_id = t03.student_id ",
                      "AND t02.year_class_id = t03.year_class_id ",
                      "AND t02.class_type_id = t03.class_type_id ",
                      "AND t02.course_version_id = t03.course_version_id",
                        "<when test='courseVersionId!=null and courseVersionId!=&apos;&apos;'>",
                            "and t02.course_version_id = ${courseVersionId}",
                        "</when>",
                        "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                            "and t01.subjects_id = ${subjectsId}",
                        "</when>",
                        //"<when test='isAll == 0'>",
                        //    "AND t02.task_course_num = t03.lesson_num",
                        //    "AND t02.task_course_num = t03.lesson_num_finish",
                        //"</when>",
                        //"<when test='isAll == 1'>",
                        //    "AND t02.task_course_num = t03.lesson_num",
                        //    "and t03.is_last = 1",
                        //"</when>",
                        "AND t02.task_course_num = t03.lesson_num",
                        "and t03.is_last = 1",
                    ") AS t",
                    "where 1=1",
                    "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; and (type == 2 || type==4)'>",
                        "and school_id in(${schoolIdStr})",
                    "</when>",
                    "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
                        "and year_class_id in(${yearClassIdStr})",
                    "</when>",
                    "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
                        "and class_type_id = ${classTypeId}",
                    "</when>",
                    "<when test='teacherId!=null and teacherId!=&apos;&apos; and type != 4'>",
                    "  and teacher_id = ${teacherId}",
                    "</when>",
                    "<when test='passType!=null and passType!=&apos;&apos; and passType == 2'>",
                    "  and lowest_shutnum &lt;= ( right_num + amend_num ) and lowest_shutnum &lt;&gt; 0",
                    "</when>",
                    "<when test='passType!=null and passType!=&apos;&apos; and passType == 3'>",
                    "  and (lowest_shutnum &gt; ( right_num + amend_num ) or (lowest_shutnum  = 0))",
                    "</when>",
                    "<when test='passType!=null and passType!=&apos;&apos; and passType == 4'>",
                    "  and lowest_shutnum &lt;= ( right_num + amend_num )",
                    "and add_date BETWEEN '${passDateStart}' AND '${passDateEnd}'",
                    "</when>",
                    "ORDER BY lesson_num,id asc",
                    "</script>"})
    public IPage<ChaPlan> getPassRadeDetail(IPage<?> page,
                                           @Param("yearPart") String yearPart,
                                         @Param("quarterNum") String quarterNum,
                                         @Param("schoolIdStr") String schoolIdStr,
                                         @Param("teacherId") String teacherId,
                                         @Param("yearClassIdStr") String yearClassIdStr,
                                         @Param("type") String type,
                                         @Param("gradeId") String gradeId,
                                         @Param("lessonNumStart") String lessonNumStart,
                                         @Param("lessonNum") String lessonNum,
                                         @Param("classTypeId") String classTypeId,
                                         @Param("readingType") String readingType,
                                         @Param("passType") String passType,
                                         @Param("lessonNow") String lessonNow,
                                         @Param("isAll") String isAll,
                                         @Param("courseVersionId") Long courseVersionId,
                                         @Param("passDate") String passDate,
                                         @Param("passDateStart") String passDateStart,
                                         @Param("passDateEnd") String passDateEnd,
                                         @Param("subjectsId") Long subjectsId,
                                         @Param("isBeforQuarter") String isBeforQuarter,
                                         @Param("readingStatusStr") String readingStatusStr,
                                         @Param("rightNumStartDate") String rightNumStartDate,
                                         @Param("rightNumEndDate") String rightNumEndDate);


    /**
     * 合计
     * @param
     * @param yearPart
     * @param quarterNum
     * @param schoolIdStr
     * @param teacherId
     * @param yearClassIdStr
     * @param type
     * @param gradeId
     * @param lessonNumStart
     * @param lessonNum
     * @param classTypeId
     * @param passType
     * @param lessonNow
     * @param isAll
     * @param courseVersionId
     * @param subjectsId
     * @param readingStatusStr
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "SUM( lowest_shutnum ) AS lowest_shutnum,",
            "SUM( right_num ) AS right_num,",
            "SUM( amend_num ) AS amend_num",
            "FROM",
            "(",
            "SELECT",
            "t01.id,",
            "t01.school_id,",
            "t01.year_part,",
            "t01.quarter_num,",
            "t02.course_version_id,",
            "t01.grade_id,",
            "t01.teacher_id,",
            "t01.class_type_id,",
            "t01.year_class_id,",
            "t01.student_id,",
            "t02.task_course_num AS lesson_num,",
            "IFNULL( t02.lowest_shutnum, 0 ) AS lowest_shutnum,",
            "IFNULL( t03.right_num, 0 ) AS right_num,",
            "IFNULL( t03.amend_num, 0 ) AS amend_num,",
            "add_date ",
            "FROM",
            "(",
            "SELECT",
            "t1.*,",
            //"t2.course_type_id AS class_type_id,",
            "t3.class_type_id AS class_type_id,",
            "t2.teacher_id ",
            "FROM",
            "xmy_erp_db.student_grade AS t1",
            "LEFT JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id ",
            "LEFT JOIN per_student_account AS t3 ON t1.student_id = t3.student_id",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum}",
            "AND t1.lesson_type = 1",
            "AND t1.deleted = 0 ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.is_last = 1 ",
            "<when test='isBeforQuarter == 0 and readingType == 1'>",
                "AND t1.reading_status IN (1) ",
            "</when>",
            "<when test='isBeforQuarter == 1 and readingType == 1'>",
                "AND t1.reading_status IN (1,5)",
            "</when>",
            "<when test='readingType == 2'>",
                "AND ((t1.reading_status IN (1,5)) or (t1.reading_status in(3,4) and use_course_count > 0)) ",
            "</when>",
            "AND t1.STATUS &lt;&gt; 3 ",
            "AND t1.deleted = 0 ",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "and t1.grade_id = ${gradeId}",
            "</when>",
            "GROUP BY",
            "student_id ",
            ") AS t01",
            "LEFT JOIN ( SELECT * FROM edu_task WHERE deleted = 0 and task_course_num BETWEEN ${lessonNumStart} and ${lessonNum}) AS t02 ON t01.year_part = t02.year_part ",
            "AND t01.quarter_num = t02.quarter_num ",
            "AND t01.subjects_id = t02.subjects_id ",
            "AND t01.class_type_id = t02.class_type_id",
            "AND t01.year_class_id = t02.year_class_id",
            "LEFT JOIN (",
            "SELECT",
            "student_id,",
            "add_date,",
            "lesson_num_finish,",
            "year_class_id,",
            "course_version_id,",
            "class_type_id,",
            "year_part,",
            "quarter_num,",
            "IFNULL( lesson_num, 0 ) AS lesson_num,",
            "right_num,",
            "is_last,",
            "amend_num ",
            "FROM",
            "cha_plan ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum}",
            "<when test='rightNumStartDate != &apos;&apos; and rightNumEndDate != &apos;&apos;'>",
                "AND add_date BETWEEN '${rightNumStartDate}' AND '${rightNumEndDate}' ",
            "</when>",
            ") AS t03 ON t01.year_part = t03.year_part ",
            "AND t01.quarter_num = t03.quarter_num ",
            "AND t01.student_id = t03.student_id ",
            "AND t02.year_class_id = t03.year_class_id ",
            "AND t02.class_type_id = t03.class_type_id ",
            "AND t02.course_version_id = t03.course_version_id",
            "<when test='courseVersionId!=null and courseVersionId!=&apos;&apos;'>",
            "and t02.course_version_id = ${courseVersionId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t01.subjects_id = ${subjectsId}",
            "</when>",
            //"<when test='isAll == 0'>",
            //    "AND t02.task_course_num = t03.lesson_num",
            //    "AND t02.task_course_num = t03.lesson_num_finish",
            //"</when>",
            //"<when test='isAll == 1'>",
            //    "AND t02.task_course_num = t03.lesson_num",
            //    "and t03.is_last = 1",
            //"</when>",
            "AND t02.task_course_num = t03.lesson_num",
            "and t03.is_last = 1",
            ") AS t",
            "where 1=1",
            "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; and (type == 2 || type==4)'>",
            "and school_id in(${schoolIdStr})",
            "</when>",
            "<when test='yearClassIdStr!=null and yearClassIdStr!=&apos;&apos; '>",
            "and year_class_id in(${yearClassIdStr})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos; '>",
            "and class_type_id = ${classTypeId}",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; and type != 4'>",
            "  and teacher_id = ${teacherId}",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 2'>",
            "  and lowest_shutnum &lt;= ( right_num + amend_num ) and lowest_shutnum &lt;&gt; 0",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 3'>",
            "  and (lowest_shutnum &gt; ( right_num + amend_num ) or (lowest_shutnum  = 0))",
            "</when>",
            "<when test='passType!=null and passType!=&apos;&apos; and passType == 4'>",
            "  and lowest_shutnum &lt;= ( right_num + amend_num )",
            "and add_date BETWEEN '${passDateStart}' AND '${passDateEnd}'",
            "</when>",
            "</script>"})
    public ChaPlan getPassRadeDetailTotal(@Param("yearPart") String yearPart,
                                            @Param("quarterNum") String quarterNum,
                                            @Param("schoolIdStr") String schoolIdStr,
                                            @Param("teacherId") String teacherId,
                                            @Param("yearClassIdStr") String yearClassIdStr,
                                            @Param("type") String type,
                                            @Param("gradeId") String gradeId,
                                            @Param("lessonNumStart") String lessonNumStart,
                                            @Param("lessonNum") String lessonNum,
                                            @Param("classTypeId") String classTypeId,
                                            @Param("readingType") String readingType,
                                            @Param("passType") String passType,
                                            @Param("lessonNow") String lessonNow,
                                            @Param("isAll") String isAll,
                                            @Param("courseVersionId") Long courseVersionId,
                                            @Param("passDate") String passDate,
                                            @Param("passDateStart") String passDateStart,
                                            @Param("passDateEnd") String passDateEnd,
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("isBeforQuarter") String isBeforQuarter,
                                            @Param("readingStatusStr") String readingStatusStr,
                                          @Param("rightNumStartDate") String rightNumStartDate,
                                          @Param("rightNumEndDate") String rightNumEndDate);



    @Select({"<script>",
            "SELECT",
                    "*,",
                    "( CASE WHEN lowest_shutnum &lt;= ( right_num + amend_num ) and lowest_shutnum  &lt;&gt; 0  THEN 1 ELSE 0 END ) AS passType",
                    "FROM",
                    "(",
                    "SELECT",
                      "t01.id,",
                      "t01.school_id,",
                      "t01.year_part,",
                      "t01.quarter_num,",
                      "t02.course_version_id,",
                      "t01.grade_id,",
                      "t01.teacher_id,",
                      "t01.class_type_id,",
                      "t01.class_type_name,",
                      "t01.year_class_id,",
                      "t01.student_id,",
                      "t02.task_course_num AS lesson_num,",
                      "IFNULL( t02.lowest_shutnum, 0 ) AS lowest_shutnum,",
                      "IFNULL( t03.right_num, 0 ) AS right_num,",
                      "IFNULL( t03.amend_num, 0 ) AS amend_num,",
                      "add_date ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.*,",
                        "t3.class_type_id AS class_type_id,",
                        "t4.name as class_type_name,",
                        "t2.teacher_id ",
                      "FROM",
                        "xmy_erp_db.student_grade AS t1",
                        "LEFT JOIN xmy_erp_db.grade AS t2 ON t1.grade_id = t2.id",
                        "LEFT JOIN per_student_account AS t3 ON t1.student_id = t3.student_id ",
                        "LEFT JOIN edu_class_type AS t4 ON t3.class_type_id = t4.id",
                      "WHERE",
                        "t1.year_part = ${yearPart} ",
                        "AND t1.quarter_num = ${quarterNum} ",
                        "AND t1.lesson_type = 1 ",
                        "AND t1.deleted = 0 ",
                        "AND t1.subjects_id = ${subjectsId} ",
                        "AND t1.is_last = 1 ",
                        "AND t1.reading_status IN ( 1 ) ",
                        "AND t1.STATUS &lt;&gt; 3 ",
                        "AND t1.deleted = 0 ",
                      "GROUP BY",
                        "student_id ",
                      ") AS t01",
                      "LEFT JOIN ( SELECT * FROM edu_task WHERE deleted = 0 AND task_course_num &lt;= ${lessonNow} ) AS t02 ON t01.year_part = t02.year_part ",
                      "AND t01.quarter_num = t02.quarter_num ",
                      "AND t01.subjects_id = t02.subjects_id ",
                      "AND t01.class_type_id = t02.class_type_id ",
                      "AND t01.year_class_id = t02.year_class_id",
                      "LEFT JOIN (",
                      "SELECT",
                        "student_id,",
                        "add_date,",
                        "lesson_num_finish,",
                        "year_class_id,",
                        "course_version_id,",
                        "class_type_id,",
                        "year_part,",
                        "quarter_num,",
                        "IFNULL( lesson_num, 0 ) AS lesson_num,",
                        "right_num,",
                        "is_last,",
                        "amend_num ",
                      "FROM",
                        "cha_plan ",
                      "WHERE",
                        "year_part = ${yearPart} ",
                        "AND quarter_num = ${quarterNum} ",
                      ") AS t03 ON t01.year_part = t03.year_part ",
                      "AND t01.quarter_num = t03.quarter_num ",
                      "AND t01.student_id = t03.student_id ",
                      "AND t02.year_class_id = t03.year_class_id ",
                      "AND t02.class_type_id = t03.class_type_id ",
                      "AND t02.course_version_id = t03.course_version_id ",
                      "AND t02.course_version_id = ${courseVersionId} ",
                      "AND t01.subjects_id = ${subjectsId} ",
                      "AND t02.task_course_num = t03.lesson_num ",
                      "AND t03.is_last = 1 ",
                    ") AS t ",
                    "WHERE",
                    "1 = 1 ",
                    "AND student_id = ${studentId} ",
                    "ORDER BY",
                    "lesson_num,",
                    "id DESC ",
            "</script>"})
    public List<ChaPlan> getPassRadeDetailForStudentId(
                                            @Param("yearPart") String yearPart,
                                            @Param("quarterNum") String quarterNum,
                                            @Param("studentId") String studentId,
                                            @Param("courseVersionId") String courseVersionId,
                                            @Param("subjectsId") String subjectsId,
                                            @Param("lessonNow") String lessonNow);




    @Select("select * from cha_plan where year_part = ${yearPart} and quarter_num = ${quarterNum} and year_class_id =${yearClassId} and class_type_id =${classTypeId} and student_id = ${studentId} and lesson_num = ${lessonNum} and course_version_id = ${courseVersionId} and subjects_id = ${subjectsId} ORDER BY id desc limit 1")
    public ChaPlan getLastOne(@Param("yearPart") Integer yearPart,
                              @Param("quarterNum") Integer quarterNum,
                              @Param("studentId") Long studentId,
                              @Param("yearClassId") Integer yearClassId,
                              @Param("classTypeId") Long classTypeId,
                              @Param("lessonNum") Integer lessonNum,
                              @Param("courseVersionId") Long courseVersionId,
                              @Param("subjectsId") Long subjectsId);

    @Select("select * from cha_plan where year_part = ${yearPart} and quarter_num = ${quarterNum} and year_class_id =${yearClassId} and class_type_id =${classTypeId} and student_id = ${studentId} and lesson_num = ${lessonNum} and lesson_num_finish = ${lessonNumFinish} and course_version_id = ${courseVersionId} and subjects_id = ${subjectsId} ORDER BY id desc limit 1")
    public ChaPlan getOneForFinishNum(@Param("yearPart") Integer yearPart,
                              @Param("quarterNum") Integer quarterNum,
                              @Param("studentId") Long studentId,
                              @Param("yearClassId") Integer yearClassId,
                              @Param("classTypeId") Long classTypeId,
                              @Param("lessonNum") Integer lessonNum,
                              @Param("lessonNumFinish") Integer lessonNumFinish,
                              @Param("courseVersionId") Long courseVersionId,
                              @Param("subjectsId") Long subjectsId);

    @Update("update cha_plan set is_last = ${isLast} where year_part = ${yearPart} and quarter_num = ${quarterNum} and year_class_id =${yearClassId} and class_type_id =${classTypeId} and student_id = ${studentId} and lesson_num = ${lessonNum} and course_version_id = ${courseVersionId} and subjects_id = ${subjectsId}")
    public boolean updateIsLast(@Param("yearPart") Integer yearPart,
                                @Param("quarterNum") Integer quarterNum,
                                @Param("studentId") Long studentId,
                                @Param("yearClassId") Integer yearClassId,
                                @Param("classTypeId") Long classTypeId,
                                @Param("lessonNum") Integer lessonNum,
                                @Param("isLast") Integer isLast,
                                @Param("courseVersionId") Long courseVersionId,
                                @Param("subjectsId") Long subjectsId
                                );

    @Update("update cha_plan set is_finish = ${isFinish} where year_part = ${yearPart} and quarter_num = ${quarterNum} and year_class_id =${yearClassId} and class_type_id =${classTypeId} and student_id = ${studentId} and lesson_num = ${lessonNum} and course_version_id = ${courseVersionId} and subjects_id = ${subjectsId}")
    public boolean updateIsFinish(@Param("yearPart") Integer yearPart,
                                @Param("quarterNum") Integer quarterNum,
                                @Param("studentId") Long studentId,
                                @Param("yearClassId") Integer yearClassId,
                                @Param("classTypeId") Long classTypeId,
                                @Param("lessonNum") Integer lessonNum,
                                @Param("isFinish") Integer isFinish,
                                @Param("courseVersionId") Long courseVersionId,
                                @Param("subjectsId") Long subjectsId);
}
